package com.koron.operation.classes.form.excel;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.koron.common.core.config.ThreadLocalContext;
import com.koron.operation.classes.form.bean.dto.ClassesFormDTO;
import com.koron.operation.classes.form.bean.dto.ClassesFormRankDTO;
import com.koron.operation.classes.form.bean.dto.ClassesFormRowDTO;
import com.koron.operation.classes.form.bean.po.ClassesFormRow;
import com.koron.operation.classes.form.bean.po.ClassesFormShift;
import com.koron.operation.classes.form.bean.po.ClassesFormUser;
import com.koron.operation.classes.form.service.ClassesFormService;
import lombok.RequiredArgsConstructor;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.koron.ebs.mybatis.ADOConnection;
import org.springframework.util.Assert;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @author jzp
 * @since 2023/6/2
 */
@RequiredArgsConstructor
public class ClassesFormWriteHandler implements SheetWriteHandler {

    private final Boolean isExport;
    private final ClassesFormService classesFormService;
    private final ClassesFormDTO classesFormDTO;

    private String getCurrDs() {
        return ThreadLocalContext.get().getCurrDs();
    }

    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        validData(classesFormDTO);
        initDealCell();
        for (ExcelCoordinateUtil.ClassesFormCell formCell : ExcelCoordinateUtil.ClassesFormCell.values()) {
            if (formCell.getDealConsumer() != null) {
                formCell.getDealConsumer().accept(sheet);
            }
        }
    }

    private void validData(ClassesFormDTO classesFormDTO) {
        // 名字，日期不能为空
        Assert.notNull(classesFormDTO.getName(), "标题不能为空");
        Assert.notNull(classesFormDTO.getShiftTime(), "排班年月不能为空");
        Assert.notNull(classesFormDTO.getDeptId(), "值班单位不能为空");
    }

    public void initDealCell() {
        ExcelCoordinateUtil.ClassesFormCell.TITLE.setDealConsumer(this::fillTitleCell);
        ExcelCoordinateUtil.ClassesFormCell.YEAR.setDealConsumer(this::fillYearCell);
        ExcelCoordinateUtil.ClassesFormCell.MONTH.setDealConsumer(this::fillMonthCell);
        ExcelCoordinateUtil.ClassesFormCell.DAY.setDealConsumer(this::fillDayCell);
        ExcelCoordinateUtil.ClassesFormCell.DATE_ROW.setDealConsumer(this::fillDateRowCell);
        ExcelCoordinateUtil.ClassesFormCell.WEEK_ROW.setDealConsumer(this::fillWeekRowCell);
        ExcelCoordinateUtil.ClassesFormCell.SHIFT_ROW.setDealConsumer(this::fillShiftRowCell);
    }

    /**
     * 填充标题
     */
    public void fillTitleCell(Sheet sheet) {
        ExcelCoordinateUtil.ClassesFormCell formCell = ExcelCoordinateUtil.ClassesFormCell.TITLE;
        ExcelCoordinateUtil.setValue(sheet, formCell.getRowIndex(), formCell.getCollIndex(), classesFormDTO.getName());
    }

    /**
     * 填充年份
     */
    public void fillYearCell(Sheet sheet) {
        ExcelCoordinateUtil.ClassesFormCell formCell = ExcelCoordinateUtil.ClassesFormCell.YEAR;
        ExcelCoordinateUtil.setValue(sheet, formCell.getRowIndex(), formCell.getCollIndex(), DateUtil.year(classesFormDTO.getShiftTime()));
    }

    /**
     * 填充月份
     */
    public void fillMonthCell(Sheet sheet) {
        ExcelCoordinateUtil.ClassesFormCell formCell = ExcelCoordinateUtil.ClassesFormCell.MONTH;
        ExcelCoordinateUtil.setValue(sheet, formCell.getRowIndex(), formCell.getCollIndex(), DateUtil.month(classesFormDTO.getShiftTime()) + 1);
    }

    /**
     * 填充月天数
     * excel自动计算
     */
    public void fillDayCell(Sheet sheet) {
        // 计算日期
        HSSFFormulaEvaluator.evaluateAllFormulaCells(sheet.getWorkbook());
        sheet.getWorkbook().setForceFormulaRecalculation(true);
        ExcelCoordinateUtil.ClassesFormCell formCell = ExcelCoordinateUtil.ClassesFormCell.DAY;
        double value = ExcelCoordinateUtil.cellDecorator(sheet, formCell.getRowIndex(), formCell.getCollIndex(), Cell::getNumericCellValue);
        // 移除后续列
        ExcelCoordinateUtil.trimRightColumn(sheet, (int) value);
    }

    /**
     * 填充日期
     */
    public void fillDateRowCell(Sheet sheet) {
        ExcelCoordinateUtil.ClassesFormCell formCell = ExcelCoordinateUtil.ClassesFormCell.DATE_ROW;
    }

    /**
     * 填充周
     * excel自动计算
     */
    public void fillWeekRowCell(Sheet sheet) {
        // excel自动计算
    }

    /**
     * 填充数据
     */
    public void fillShiftRowCell(Sheet sheet) {
        if (isExport) {
            Assert.notEmpty(classesFormDTO.getRowList(), "排班数据不能为空");
            // 填充班次信息
            fillShift(sheet);
        } else {
            // 填充岗位人员信息
            fillStaff(sheet);
        }
    }

    /**
     * 填充岗位人员信息
     */
    private void fillStaff(Sheet sheet) {
        ExcelCoordinateUtil.ClassesFormCell formCell = ExcelCoordinateUtil.ClassesFormCell.SHIFT_ROW;
        List<ClassesFormRankDTO> classesFormRankDTOList = ADOConnection.runTask(getCurrDs(), classesFormService, "getUserListByOrgId", List.class, classesFormDTO.getDeptId());
        boolean isCreateRank = false;
        boolean isCreateUser = false;
        int rowIndex = formCell.getRowIndex();
        Row rankRow = sheet.getRow(rowIndex);
        short defaultRowHeight = rankRow.getHeight();
        ExcelCoordinateUtil.ClassesFormCell dayformcell = ExcelCoordinateUtil.ClassesFormCell.DAY;
        double value = ExcelCoordinateUtil.cellDecorator(sheet, dayformcell.getRowIndex(), dayformcell.getCollIndex(), Cell::getNumericCellValue);
        short lastCellNum = (short) value;
        CellRangeAddress rankMerge = getRankMerge(sheet);

        // rowIndex， type 0岗位 1拼音 2姓名
        HashMap<Integer, Integer> cellFormTypeMap = new HashMap<>();

        // 获取岗位人员
        for (int i = 0; i < classesFormRankDTOList.size(); i++) {
            ClassesFormRankDTO classesFormRankDTO = classesFormRankDTOList.get(i);
            if (CollUtil.isEmpty(classesFormRankDTO.getUsers())) {
                continue;
            }
            String rank = classesFormRankDTO.getRankPinyin() + "\n" + classesFormRankDTO.getRankName();

            if (isCreateRank) {
                sheet.createRow(rowIndex).setHeight(defaultRowHeight);
                CellRangeAddress copy = rankMerge.copy();
                copy.setFirstRow(rowIndex);
                copy.setLastRow(rowIndex);
                sheet.addMergedRegion(copy);
                cellFormTypeMap.put(rowIndex, 0);
            }
            isCreateRank = true;
            ExcelCoordinateUtil.setValue(sheet, rowIndex++, 0, rank);
            // 填充人员
            for (int j = 0; j < classesFormRankDTO.getUsers().size(); j++) {
                ClassesFormUser classesFormUser = classesFormRankDTO.getUsers().get(j);
                if (isCreateUser) {
                    sheet.createRow(rowIndex).setHeight(defaultRowHeight);
                    cellFormTypeMap.put(rowIndex, 1);
                    ExcelCoordinateUtil.rowFillNull(sheet, rowIndex, 1, lastCellNum, false);
                    sheet.createRow(rowIndex + 1).setHeight(defaultRowHeight);
                    cellFormTypeMap.put(rowIndex + 1, 2);
                    ExcelCoordinateUtil.rowFillNull(sheet, rowIndex + 1, 1, lastCellNum, false);


                }
                isCreateUser = true;
                ExcelCoordinateUtil.setValue(sheet, rowIndex++, 0, classesFormUser.getUserPinyin());
                ExcelCoordinateUtil.setValue(sheet, rowIndex++, 0, classesFormUser.getUserName());


            }
        }

        // 收集几种单元格格式
        // 1、岗位单元格
        CellStyle rankKey = rankRow.getCell(0).getCellStyle();
        // 2、岗位值
        CellStyle rankValue = rankRow.getCell(1).getCellStyle();
        // 3、拼音key
        CellStyle pinyinKey = sheet.getRow(formCell.getRowIndex()+1).getCell(0).getCellStyle();
        // 4、拼音value
        CellStyle pinyinValue = sheet.getRow(formCell.getRowIndex()+1).getCell(1).getCellStyle();
        // 3、姓名key
        CellStyle nameKey = sheet.getRow(formCell.getRowIndex()+2).getCell(0).getCellStyle();
        // 4、姓名value
        CellStyle nameValue = sheet.getRow(formCell.getRowIndex()+2).getCell(1).getCellStyle();

        // 设置单元格
        for (int i = formCell.getRowIndex() + 3; i < rowIndex; i++) {
            Row row = sheet.getRow(i);
            Iterator<Cell> cellIterator = row.iterator();
            Cell cell = cellIterator.next();
            switch (cellFormTypeMap.get(i)) {
                case 0:
                    cell.setCellStyle(rankKey);
                    break;
                case 1:
                    cell.setCellStyle(pinyinKey);
                    break;
                case 2:
                    cell.setCellStyle(nameKey);
                    break;
                default:
                    break;
            }
            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                switch (cellFormTypeMap.get(i)) {
                    case 0:
                        cell.setCellStyle(rankValue);
                        break;
                    case 1:
                        cell.setCellStyle(pinyinValue);
                        break;
                    case 2:
                        cell.setCellStyle(nameValue);
                        break;
                    default:
                        break;
                }
            }

            // 最后一列单独计算
            Cell lastCell = row.createCell(lastCellNum + 1);
            CellStyle cellStyle = lastCell.getCellStyle();
            cellStyle.cloneStyleFrom(cellStyle);
            cellStyle.setBorderLeft(BorderStyle.MEDIUM);
            lastCell.setCellStyle(cellStyle);
        }

        // 设置最后一行
        Row row = sheet.getRow(rowIndex-1);

        CellStyle lastRowStyle = sheet.getWorkbook().createCellStyle();
        CellStyle cellStyle = row.getCell(0).getCellStyle();
        lastRowStyle.cloneStyleFrom(cellStyle);
        lastRowStyle.setBorderBottom(BorderStyle.MEDIUM);
        row.getCell(0).setCellStyle(lastRowStyle);

        CellStyle lastRowStyle1 = sheet.getWorkbook().createCellStyle();
        CellStyle cellStyle1 = row.getCell(1).getCellStyle();
        lastRowStyle1.cloneStyleFrom(cellStyle1);
        lastRowStyle1.setBorderBottom(BorderStyle.MEDIUM);
        row.getCell(1).setCellStyle(lastRowStyle1);

        ExcelCoordinateUtil.rowFillNull(sheet, rowIndex-1, 2, lastCellNum, true);

    }

    /**
     * 填充班次信息
     */
    private void fillShift(Sheet sheet) {
        ExcelCoordinateUtil.ClassesFormCell formCell = ExcelCoordinateUtil.ClassesFormCell.SHIFT_ROW;
        boolean isCreateRank = false;
        boolean isCreateUser = false;
        int rowIndex = formCell.getRowIndex();
        Row rankRow = sheet.getRow(rowIndex);
        short defaultRowHeight = rankRow.getHeight();
        ExcelCoordinateUtil.ClassesFormCell dayformcell = ExcelCoordinateUtil.ClassesFormCell.DAY;
        double value = ExcelCoordinateUtil.cellDecorator(sheet, dayformcell.getRowIndex(), dayformcell.getCollIndex(), Cell::getNumericCellValue);
        short lastCellNum = (short) value;
        CellRangeAddress rankMerge = getRankMerge(sheet);

        // rowIndex， type 0岗位 1拼音 2姓名
        HashMap<Integer, Integer> cellFormTypeMap = new HashMap<>();

        // 获取岗位人员
        List<ClassesFormRowDTO> rowList = classesFormDTO.getRowList();
        // 岗位是手填的，不一定有岗位id
        Map<String, List<ClassesFormRowDTO>> rankMap = rowList.stream().collect(Collectors.groupingBy(ClassesFormRow::getRankName, LinkedHashMap::new, Collectors.toList()));
        for (List<ClassesFormRowDTO> classesFormRowDTOS : rankMap.values()) {
            ClassesFormRowDTO classesFormRowDTO = classesFormRowDTOS.get(0);
            if (CollUtil.isEmpty(classesFormRowDTO.getShiftList())) {
                continue;
            }
            String rank = classesFormRowDTO.getRankPinyin() + "\n" + classesFormRowDTO.getRankName();

            if (isCreateRank) {
                sheet.createRow(rowIndex).setHeight(defaultRowHeight);
                CellRangeAddress copy = rankMerge.copy();
                copy.setFirstRow(rowIndex);
                copy.setLastRow(rowIndex);
                sheet.addMergedRegion(copy);
                cellFormTypeMap.put(rowIndex, 0);
            }
            isCreateRank = true;
            ExcelCoordinateUtil.setValue(sheet, rowIndex++, 0, rank);
            // 填充人员
            for (int j = 0; j < classesFormRowDTOS.size(); j++) {
                ClassesFormRowDTO rowDTO = classesFormRowDTOS.get(j);
                if (isCreateUser) {
                    sheet.createRow(rowIndex).setHeight(defaultRowHeight);
                    cellFormTypeMap.put(rowIndex, 1);
                    sheet.createRow(rowIndex + 1).setHeight(defaultRowHeight);
                    cellFormTypeMap.put(rowIndex + 1, 2);
                }
                isCreateUser = true;
                if (CollUtil.isNotEmpty(rowDTO.getShiftList())) {
                    List<String> overItemNameList = rowDTO.getShiftList().stream()
                            .map(e -> Objects.toString(e.getChangeItemName(), "") + (e.getOverDuration() == null ? "" : "+" + e.getOverDuration()))
                            .collect(Collectors.toList());
                    ExcelCoordinateUtil.rowFill(sheet, rowIndex, 1, overItemNameList, false);
                    List<String> itemNameList = rowDTO.getShiftList().stream().map(ClassesFormShift::getClassesItemName).collect(Collectors.toList());
                    ExcelCoordinateUtil.rowFill(sheet, rowIndex + 1, 1, itemNameList, false);
                }
                ExcelCoordinateUtil.setValue(sheet, rowIndex++, 0, rowDTO.getUserPinyin());
                ExcelCoordinateUtil.setValue(sheet, rowIndex++, 0, rowDTO.getUserName());
            }
        }

        // 收集几种单元格格式
        // 1、岗位单元格
        CellStyle rankKey = rankRow.getCell(0).getCellStyle();
        // 2、岗位值
        CellStyle rankValue = rankRow.getCell(1).getCellStyle();
        // 3、拼音key
        CellStyle pinyinKey = sheet.getRow(formCell.getRowIndex()+1).getCell(0).getCellStyle();
        // 4、拼音value
        CellStyle pinyinValue = sheet.getRow(formCell.getRowIndex()+1).getCell(1).getCellStyle();
        // 3、姓名key
        CellStyle nameKey = sheet.getRow(formCell.getRowIndex()+2).getCell(0).getCellStyle();
        // 4、姓名value
        CellStyle nameValue = sheet.getRow(formCell.getRowIndex()+2).getCell(1).getCellStyle();

        // 设置单元格
        for (int i = formCell.getRowIndex() + 3; i < rowIndex; i++) {
            Row row = sheet.getRow(i);
            Iterator<Cell> cellIterator = row.iterator();
            Cell cell = cellIterator.next();
            switch (cellFormTypeMap.get(i)) {
                case 0:
                    cell.setCellStyle(rankKey);
                    break;
                case 1:
                    cell.setCellStyle(pinyinKey);
                    break;
                case 2:
                    cell.setCellStyle(nameKey);
                    break;
                default:
                    break;
            }
            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                switch (cellFormTypeMap.get(i)) {
                    case 0:
                        cell.setCellStyle(rankValue);
                        break;
                    case 1:
                        cell.setCellStyle(pinyinValue);
                        break;
                    case 2:
                        cell.setCellStyle(nameValue);
                        break;
                    default:
                        break;
                }
            }

            // 最后一列单独计算
            Cell lastCell = row.createCell(lastCellNum + 1);
            CellStyle cellStyle = lastCell.getCellStyle();
            cellStyle.cloneStyleFrom(cellStyle);
            cellStyle.setBorderLeft(BorderStyle.MEDIUM);
            lastCell.setCellStyle(cellStyle);
        }

        // 设置最后一行
        Row row = sheet.getRow(rowIndex-1);

        CellStyle lastRowStyle = sheet.getWorkbook().createCellStyle();
        CellStyle cellStyle = row.getCell(0).getCellStyle();
        lastRowStyle.cloneStyleFrom(cellStyle);
        lastRowStyle.setBorderBottom(BorderStyle.MEDIUM);
        row.getCell(0).setCellStyle(lastRowStyle);

        CellStyle lastRowStyle1 = sheet.getWorkbook().createCellStyle();
        CellStyle cellStyle1 = row.getCell(1).getCellStyle();
        lastRowStyle1.cloneStyleFrom(cellStyle1);
        lastRowStyle1.setBorderBottom(BorderStyle.MEDIUM);
        row.getCell(1).setCellStyle(lastRowStyle1);

        ExcelCoordinateUtil.rowFillNull(sheet, rowIndex-1, 2, lastCellNum, true);
    }

    private CellRangeAddress getRankMerge(Sheet sheet) {
        return sheet.getMergedRegions().stream().filter(e -> e.getFirstRow() == 9).findFirst().orElse(null);
    }

}
